package com.qikux.dao.impl;

import com.qikux.annotation.Component;
import com.qikux.dao.MessageDao;
import com.qikux.entity.Notice;
import com.qikux.utils.DBUtils;
import com.qikux.vo.Page;

import java.util.List;
import java.util.Map;

@Component
public class MessageDaoImpl implements MessageDao {

    /**
     * @description 查询用户是否拉黑对方 或被 对方拉黑
     * @date 2022/9/29 11:33
     * @auther XuHuanDong
     * @return
     */
    @Override
    public List<Map<String, Object>> findBlackStatusByUserId(Long id, Long resUserId) {
        String sql = """
                select user_id,user_id_black,create_time from tb_user_black where (user_id = ? or user_id = ?) 
                and (user_id_black = ? or user_id_black = ?)
                """;
        return DBUtils.queryForList(sql, id, resUserId, id, resUserId);
    }

    /**
     * @description 查询官方通知消息
     * @date 2022/10/10 21:27
     * @auther XuHuanDong
     * @return
     */
    @Override
    public List<Map<String, Object>> findAllAnnouncement() {
        String sql = "select type , content , create_time from tb_announcement";
        return DBUtils.queryForList(sql);
    }

    /**
     * @description 根据用户id 查询用户所有通知消息
     * @date 2022/9/29 19:55
     * @auther XuHuanDong
     * @return
     */
    @Override
    public List<Notice> findAllNotifiByUserId(Long id) {
        String sql = """
                select remark,content,user_id,tag_user_id,res_id,create_time
                from tb_notice where user_id = ? order by create_time desc
                """;
        return DBUtils.queryForList(sql,Notice.class,id);

    }
    @Override
    public List<Map<String, Object>> findMsgList(int id, Page<Map<String, Object>> pageBean) {
        /*String sql = """
                select * from(
                select send_time,user_id,nick_name,photo from tb_user_info
                join tb_user_letter on user_id = receive_id
                where send_id = ?
                ORDER BY  send_time desc) as s
                GROUP BY s.user_id limit ?,?

                select user_id_b id from tb_user_focus where user_id_a =? and status !=0
                """;*/
        String sql = """
                select nick_name,user_id,photo from tb_user_info where user_id in(
                select * from (
                select user_id_a id from tb_user_focus where user_id_b=? and status !=0 union all
                select user_id_b id from tb_user_focus where user_id_a=? and status !=0
                ) p where id
                not in(
                select user_id_black from tb_user_black where user_id=? union all
                select user_id from tb_user_black  where user_id_black=?
                )) GROUP BY id limit ?,?
                """;
        return DBUtils.queryForList(sql,id,id,id,id,pageBean.getOffset(),pageBean.getRows());
    }

    @Override
    public int findMsgCount(int id) {
        String sql = """
                select count(1) from tb_user_info where user_id in(
                select receive_id from tb_user_letter l where send_id = ? GROUP BY receive_id)
                """;
        return DBUtils.execute(sql,rs -> {
            if (rs.next()){
                return rs.getInt(1);
            }
            return 0;
        },id);
    }

    @Override
    public List<Map<String, Object>> findUserMsg(Long sendid, int reviceid) {
        String sql = """
                select send_id,receive_id,message,send_time ,nick_name,photo from tb_user_letter l
                join tb_user_info i on l.send_id = i.user_id
                where (send_id=? and receive_id=?)
                or (send_id=? and receive_id=?) ORDER BY send_time asc
                """;
        return DBUtils.queryForList(sql,sendid,reviceid,reviceid,sendid);
    }

    @Override
    public int sendMsgs(Long sendid, String recvid, String message) {

        String sql = """
                      insert into tb_user_letter (send_id,receive_id,message,send_time)
                      values(?,?,?,now())
                """;

        return DBUtils.update(sql,sendid,recvid,message);
    }

    @Override
    public int upblackUser(Long sendid, int bid) {
        String sql = """
                insert into tb_user_black (user_id,user_id_black,create_time) values(?,?,now())
                """;
        return DBUtils.update(sql,sendid,bid);
    }

    @Override
    public List<Map<String, Object>> findMyBlack(Long sendid, Page<Map<String, Object>> pageBean) {
        String sql = """
                    select * from(
                    select * from
                    (SELECT user_id_black,photo,nick_name FROM tb_user_black t
                    inner join tb_user_info i where t.user_id_black=i.user_id) p
                    where user_id_black in(
                    select user_id_black from tb_user_black where user_id=?
                    )) as p GROUP BY p.user_id_black limit ?,?
                """;
        return DBUtils.queryForList(sql,sendid,pageBean.getOffset(),pageBean.getRows());
    }

    @Override
    public int findMyblackCount(Long sendid) {
        String sql = """
                select count(user_id_black) from tb_user_black where user_id = ?
                """;
        return DBUtils.execute(sql,rs -> {
            if (rs.next()){
                return rs.getInt(1);
            }
            return 0;
        },sendid);
    }

    @Override
    public int removeblack(Long sendid, int reb) {
        String sql = """
                   delete from tb_user_black where user_id= ? and user_id_black = ? 
                """;
        return DBUtils.update(sql,sendid,reb);
    }

    @Override
    public int findunRead(Long sendid) {
        String sql = """
                select count(1) from tb_user_letter where receive_id=? and message_status = 0
                """;

        return DBUtils.execute(sql,rs -> {
            if (rs.next()){
                return rs.getInt(1);
            }
            return 0;
        },sendid);
    }

    @Override
    public int upReaded(Long sendid, int upid) {
        String sql = """
                update tb_user_letter set message_status =1 where send_id =? and receive_id =?
                """;
        return DBUtils.update(sql, upid, sendid);
    }

    @Override
    public int findBlack(Long sendid, int bid) {
        String sql = """
                select count(user_id_black) from tb_user_black where user_id=? and user_id_black =?
                """;

        return DBUtils.execute(sql,rs -> {
            if (rs.next()){
                return rs.getInt(1);
            }
            return 0;
        },sendid,bid);
    }
}
